use std::vec;

use lavender::db::query_statement::prelude::*;
use lavender::*;
use lavender::db::model::prelude::*;
use lavender::db::table::{ExceResult,Table,ExecutorBase,TableExecutor};
use lavender::db::tablex::{*};
use sqlx_core::executor;

use crate::error::Result;
use sqlx::prelude::FromRow;

#[derive(Debug,Default,Clone,Copy,FromRow)]
pub struct FullData{
    pub id: i64,
    pub cid: i32,
    pub day: i32,
    pub time: i32,
    pub price: i32,
    pub qty: i32,
    pub r#type: i8,
}


pub struct BaseTableX{
    pub sql_maker: Option<SqlMakerRaw>,
    pub tables:Option<Vec<String>>,
}

impl TableX for BaseTableX{
    const TABLE_PREFIX_NAME:&'static str = "hangqing_detail";
    const DATABASE_PREFIX_NAME:&'static str ="stock_hangqing";

    const DATABASE_NAME:&'static str = "test";

    const DATABASE_NUM:i16 = 10 ;
    const TABLE_NUM:i16 = 10 ;
    lavender_default_tablex_property!();  
}


#[tokio::test]
async fn get_test(){

    let res:Result<FullData> = BaseTableX::with_sql_maker(1800,lavender_model_query!(find_by_pk 1).unwrap()).build_executor().unwrap().one_as().await;
    assert!(res.is_ok(),"query2 failed. msg:{:?}",res.err().unwrap());  
    println!("{:?}",res.unwrap());

    let executors = BaseTableX::with_sql_maker_list(vec![1756,1758,1762,1764,1765], lavender_model_query!(find_all lavender_query_filters!(
            {"cid" in_ 1756 1758 1762 1764 1765},
            {"day" 20240513}
        ),
        ColumnSqlMaker::with_literal("*"),
        "id DESC",
        10
    ).unwrap()).build_executors().unwrap();

    let mut handle = vec![];
    for mut e in executors{
        handle.push(tokio::spawn(async move{
            let d: Result<Vec<FullData>> = e.all_as().await;
            assert!(d.is_ok(),"query2 failed. msg:{:?}",d.err().unwrap()); 
            d.unwrap()
        }));
    }

    let mut all_data = vec![];
    for h in handle {
       let res = h.await;
       if res.is_ok(){
            all_data.extend(res.ok().unwrap());
       }
    }
    println!("all_data is {:?}",all_data);

    // let res = BaseTable::with_sql_maker(lavender_model_query!(find lavender_query_filter!("id" > 20)).unwrap()).build_executor().unwrap().one().await;
    // assert!(res.is_ok(),"query3 failed. msg:{:?}",res.err().unwrap());  
    // println!("{:?}",res);

    // let res = BaseTable::with_sql_maker(lavender_model_query!(find lavender_query_filter!("id" > 20),lavender_query_fields!("id","day")).unwrap()).build_executor().unwrap().one().await;
    // assert!(res.is_ok(),"query4 failed. msg:{:?}",res.err().unwrap());  
    // println!("{:?}",res);

    // let res = BaseTable::with_sql_maker(lavender_model_query!(find lavender_query_filter!("id" > 20),lavender_query_fields!("id","day"),"id DESC").unwrap()).build_executor().unwrap().one().await;
    // assert!(res.is_ok(),"query5 failed. msg:{:?}",res.err().unwrap());  
    // println!("{:?}",res);

    // let res = BaseTable::with_sql_maker(lavender_model_query!(find_all lavender_query_filter!("id" > 20)).unwrap()).build_executor().unwrap().all().await;
    // assert!(res.is_ok(),"query6 failed. msg:{:?}",res.err().unwrap());  
    // println!("{:?}",res);

    // let res = BaseTable::with_sql_maker(lavender_model_query!(find_all lavender_query_filter!("id" > 20),lavender_query_fields!("id","day")).unwrap()).build_executor().unwrap().all().await;
    // assert!(res.is_ok(),"query7 failed. msg:{:?}",res.err().unwrap());  
    // println!("{:?}",res);

    // let res = BaseTable::with_sql_maker(lavender_model_query!(find_all lavender_query_filter!("id" > 20),lavender_query_fields!("id","day"),"id DESC").unwrap()).build_executor().unwrap().all().await;
    // assert!(res.is_ok(),"query8 failed. msg:{:?}",res.err().unwrap());  
    // println!("{:?}",res);

    // let res = BaseTable::with_sql_maker(lavender_model_query!(find_all lavender_query_filter!("id" > 20),lavender_query_fields!("id","day"),"id DESC",10).unwrap()).build_executor().unwrap().all().await;
    // assert!(res.is_ok(),"query9 failed. msg:{:?}",res.err().unwrap());  
    // println!("{:?}",res);

    // let res = BaseTable::with_sql_maker(lavender_model_query!(find_all lavender_query_filter!("id" > 20),lavender_query_fields!("id","day"),"id DESC",5,10).unwrap()).build_executor().unwrap().all().await;
    // assert!(res.is_ok(),"query10 failed. msg:{:?}",res.err().unwrap());  
    // println!("{:?}",res);

    
    // let res = BaseTable::with_sql_maker(lavender_model_query!(add lavender_query_key_values!({"datenum" 123},{"day" 456},{"time" 789})).unwrap()).build_executor().unwrap().exec().await;
    // assert!(res.is_ok(),"query11 failed. msg:{:?}",res.err().unwrap());  
    // let insert_info = res.unwrap();
    // println!("{:?}",insert_info);
    // let insert_id = insert_info.last_insert_id();
    // let affected = insert_info.rows_affected();
    // assert!(affected > 0,"add data failed");

    // let res = BaseTable::with_sql_maker(lavender_model_query!(update insert_id, lavender_query_key_values!({"datenum" 0},{"day" 111},{"time" 222})).unwrap()).build_executor().unwrap().exec().await;
    // assert!(res.is_ok(),"query12 failed. msg:{:?}",res.err().unwrap());  
    // assert!(insert_info.rows_affected() > 0,"update data failed");

    // let res = BaseTable::with_sql_maker(lavender_model_query!(update insert_id, lavender_query_key_values!({"datenum" 0},{"day" 111},{"time" 222}),lavender_query_filter!("datenum" > 0)).unwrap()).build_executor().unwrap().exec().await;
    // assert!(res.is_ok(),"query13 failed. msg:{:?}",res.err().unwrap());  
    // let res = res.unwrap();
    // assert!(res.rows_affected() == 0,"update data failed {:?}",res);

    // let res = BaseTable::with_sql_maker(lavender_model_query!(update_all lavender_query_filter!("id" >= insert_id),lavender_query_key_values!({"datenum" 3},{"day" 3},{"time" 3})).unwrap()).build_executor().unwrap().exec().await;
    // assert!(res.is_ok(),"query14 failed. msg:{:?}",res.err().unwrap());
    // let res = res.unwrap();
    // assert!(res.rows_affected() > 0,"update data failed {:?}",res);

    // let res = BaseTable::with_sql_maker(lavender_model_query!(upsert lavender_query_key_values!({"id" insert_id},{"datenum" 4},{"day" 4},{"time" 4}),lavender_query_key_values!({"time" 5})).unwrap()).build_executor().unwrap().exec().await;
    // assert!(res.is_ok(),"query15 failed. msg:{:?}",res.err().unwrap());
    // println!("upsert res {:?}",res.unwrap());

    // let res = BaseTable::with_sql_maker(lavender_model_query!(delete insert_id,lavender_query_filter!("time" 5)).unwrap()).build_executor().unwrap().exec().await;
    // assert!(res.is_ok(),"query16 failed. msg:{:?}",res.err().unwrap());
    // let res = res.unwrap();
    // println!("delete res {:?}",res);

    // let res = BaseTable::with_sql_maker(lavender_model_query!(delete insert_id).unwrap()).build_executor().unwrap().exec().await;
    // assert!(res.is_ok(),"query17 failed. msg:{:?}",res.err().unwrap());
    // let res = res.unwrap();
    // println!("delete res {:?}",res);

}


// type DeviceBase = (i32,String,i16,i16,i64,i64);

// fn get_test_mutil_insert_data(len:i32) -> Vec<DeviceBase>{
//     let mut res = vec![];
//     let tn = utils::timestamp();
//     for i in 0..len  {
//         res.push((i ,format!("sn_{}",i*i), (i % 10) as i16,(i / 10 % 10) as i16,tn,tn + (i as i64) + 1));
//     }
//     res
// }

// #[tokio::test]
// async fn test_bulk(){
//     let num = 5000;
//     let mut column = lavender_query_fields!("datenum","day","time");
//     let mutil_data = get_test_mutil_insert_data(num);
//     column.push_values_convert(mutil_data, |item|{
//         format!("{},{},{}",item.5,item.0,item.3).into()
//     });

//     let model = BaseModel::bulk_insert(column).unwrap();

//     let res = BaseTable::with_sql_maker(model).build_executor().unwrap().set_persistent(false).exec().await;
//     assert!(res.is_ok(),"query1 failed. msg:{:?}",res.err().unwrap());  
//     let insert_info = res.unwrap();
//     println!("{:?}",insert_info);
//     let insert_id = insert_info.last_insert_id();
//     let affected = insert_info.rows_affected();
//     assert!(affected == num as u64,"add bulk data failed");

//     let res = BaseTable::with_sql_maker(lavender_model_query!(delete_all lavender_query_filter!("id" > insert_id),1000).unwrap()).build_executor().unwrap().exec().await;
//     assert!(res.is_ok(),"query2 failed. msg:{:?}",res.err().unwrap());
//     let res = res.unwrap();
//     println!("delete res {:?}",res);

//     let res = BaseTable::with_sql_maker(lavender_model_query!(delete_all lavender_query_filter!("id" >= insert_id)).unwrap()).build_executor().unwrap().exec().await;
//     assert!(res.is_ok(),"query2 failed. msg:{:?}",res.err().unwrap());
//     let res = res.unwrap();
//     println!("delete res {:?}",res);
// }